﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_BGM_ProjectOperationPerson : IDAL_BGM_ProjectOperationPerson
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public IList<M_BGM_ProjectOperationPerson> GetFull(Nullable<int> bgm_proj_oper_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationPerson> infoList = new List<M_BGM_ProjectOperationPerson>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);

                strSQL = "SELECT pjper.bgm_proj_oper_per_seq, pjper.bgm_proj_oper_seq"
                    + ", pjper.emp_seq, pjper.contact_seq"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", c.NameTh as contact_name"
                    + ", emp.class_seq, class.class_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN 'บุคคลภายใน'"
                        + " ELSE 'บุคคลภายนอก'"
                        + " END AS type_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " ELSE c.NameTh"
                        + " END AS per_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN pos.pos_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS per_pos_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN j.job_site_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS per_job_site_name"

                    + ", pjper.record_status"
                    + " FROM bgm_project_operation_person pjper"
                    + " LEFT JOIN IcmMasContact c ON pjper.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON pjper.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions pos ON emp.pos_seq = pos.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON emp.working_job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_mp_classes class ON emp.class_seq = class.class_seq"
                    + " WHERE pjper.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjper.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationPerson info = new M_BGM_ProjectOperationPerson();
                        info.bgm_proj_oper_per_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_per_seq"]);
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        //info.contact_person_seq = cn.ConvertNullInt(drc[i]["contact_person_seq"]);

                        info.type_name = drc[i]["type_name"].ToString();
                        info.per_name = drc[i]["per_name"].ToString();
                        info.per_pos_name = drc[i]["per_pos_name"].ToString();
                        info.per_class_name = drc[i]["class_name"].ToString();
                        info.per_job_site_name = drc[i]["per_job_site_name"].ToString();
                        M_HRM_PS_Employees subInfo1 = new M_HRM_PS_Employees();
                        M_FIM_MAS_Contacts subInfo2 = new M_FIM_MAS_Contacts();

                        subInfo1.emp_name = drc[i]["emp_name"].ToString();
                        subInfo2.contact_name = drc[i]["contact_name"].ToString();

                        info.m_hrm_ps_employees = subInfo1;
                        info.m_fim_mas_contacts = subInfo2;

                        info.record_status = drc[i]["record_status"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationPerson> GetList(Nullable<int> bgm_proj_oper_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationPerson> infoList = new List<M_BGM_ProjectOperationPerson>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);

                strSQL = "SELECT pjper.bgm_proj_oper_per_seq, pjper.bgm_proj_oper_seq"
                    + ", pjper.emp_seq, pjper.contact_seq"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", c.NameTh as contact_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN 'บุคคลภายใน'"
                        + " ELSE 'บุคคลภายนอก'"
                        + " END AS type_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " ELSE c.NameTh"
                        + " END AS per_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN pos.pos_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS per_pos_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN j.job_site_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS per_job_site_name"

                    + ", pjper.record_status"
                    + " FROM bgm_project_operation_person pjper"
                    + " LEFT JOIN IcmMasContact c ON pjper.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON pjper.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions pos ON emp.pos_seq = pos.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON emp.working_job_site_seq = j.job_site_seq"
                    + " WHERE pjper.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjper.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationPerson info = new M_BGM_ProjectOperationPerson();
                        info.bgm_proj_oper_per_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_per_seq"]);
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        //info.contact_person_seq = cn.ConvertNullInt(drc[i]["contact_person_seq"]);

                        info.type_name = drc[i]["type_name"].ToString();
                        info.per_name = drc[i]["per_name"].ToString();
                        info.per_pos_name = drc[i]["per_pos_name"].ToString();
                        info.per_job_site_name = drc[i]["per_job_site_name"].ToString();
                        M_HRM_PS_Employees subInfo1 = new M_HRM_PS_Employees();
                        M_FIM_MAS_Contacts subInfo2 = new M_FIM_MAS_Contacts();

                        subInfo1.emp_name = drc[i]["emp_name"].ToString();
                        subInfo2.contact_name = drc[i]["contact_name"].ToString();

                        info.m_hrm_ps_employees = subInfo1;
                        info.m_fim_mas_contacts = subInfo2;

                        info.record_status = drc[i]["record_status"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_BGM_ProjectOperationPerson GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_ProjectOperationPerson info = new M_BGM_ProjectOperationPerson();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT pjper.bgm_proj_oper_per_seq, pjper.bgm_proj_oper_seq"
                    + ", pjper.emp_seq, pjper.contact_seq"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", c.NameTh as contact_name"
                    + ", emp.class_seq, class.class_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN 'บุคคลภายใน'"
                        + " ELSE 'บุคคลภายนอก'"
                        + " END AS type_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " ELSE c.NameTh"
                        + " END AS per_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN pos.pos_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS per_pos_name"

                    + ", CASE WHEN pjper.emp_seq is not null THEN j.job_site_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS per_job_site_name"

                    + ", pjper.record_status"
                    + " FROM bgm_project_operation_person pjper"
                    + " LEFT JOIN IcmMasContact c ON pjper.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON pjper.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions pos ON emp.pos_seq = pos.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON emp.working_job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_mp_classes class ON emp.class_seq = class.class_seq"
                    + " WHERE pjper.bgm_proj_oper_per_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_proj_oper_per_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_per_seq"]);
                    info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.contact_seq = cn.ConvertNullInt(drc[0]["contact_seq"]);
                    //info.contact_person_seq = cn.ConvertNullInt(drc[0]["contact_person_seq"]);

                    info.type_name = drc[0]["type_name"].ToString();
                    info.per_name = drc[0]["per_name"].ToString();
                    info.per_pos_name = drc[0]["per_pos_name"].ToString();
                    info.per_class_name = drc[0]["class_name"].ToString();
                    info.per_job_site_name = drc[0]["per_job_site_name"].ToString();
                    M_HRM_PS_Employees subInfo1 = new M_HRM_PS_Employees();
                    M_FIM_MAS_Contacts subInfo2 = new M_FIM_MAS_Contacts();

                    subInfo1.emp_name = drc[0]["emp_name"].ToString();
                    subInfo2.contact_name = drc[0]["contact_name"].ToString();

                    info.m_hrm_ps_employees = subInfo1;
                    info.m_fim_mas_contacts = subInfo2;

                    info.record_status = drc[0]["record_status"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_BGM_ProjectOperationPerson info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@bgm_proj_oper_per_seq", info.bgm_proj_oper_per_seq);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(3, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(4, "@contact_person_seq", "");

                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@created_by", info.created_by);
                dbManager.AddParameters(7, "@created_when", info.created_when);

                strSQL = "INSERT INTO bgm_project_operation_person"
                    + "(bgm_proj_oper_seq, emp_seq, contact_seq"//, contact_person_seq"
                    + ", record_status, created_by, created_when)"
                    + " VALUES (@bgm_proj_oper_seq"
                        + ", @emp_seq"
                        + ", @contact_seq"
                        //+ ", @contact_person_seq"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_BGM_ProjectOperationPerson info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@bgm_proj_oper_per_seq", info.bgm_proj_oper_per_seq);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(3, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(4, "@contact_person_seq", "");

                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@updated_by", info.updated_by);
                dbManager.AddParameters(7, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_person"
                    + " SET bgm_proj_oper_seq = @bgm_proj_oper_seq"
                    + ", emp_seq = @emp_seq"
                    + ", contact_seq = @contact_seq"
                    //+ ", contact_person_seq = @contact_person_seq"
                    
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_per_seq = @bgm_proj_oper_per_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_BGM_ProjectOperationPerson info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_oper_per_seq", info.bgm_proj_oper_per_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_person"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_per_seq = @bgm_proj_oper_per_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
